function rslt = get_info_from_database( mode, varargin)
% This function returns:
% - security_id: if the input is a short_name, mode 'sec-id'
% - name and short_name: if the input is a security_id, mode 'name'
% - trading_destination_id: if the input is a name/short name
% - data of one security with a given security_id, a give begin and end
% date
%
% Example:
% rslt = get_info_from_database('sec_id', 'AAA')
% rslt = get_info_from_database('name', 1)
% rslt = get_info_from_database('ticker', 1)
% rslt = get_info_from_database('ticksize', 1)
% rslt = get_info_from_database('trading_destination', 1)
% rslt = get_info_from_database('trading_destination', 'AAA')
% rslt = get_info_from_database('trading_destination_id', 1)
% rslt = get_info_from_database('trading_destination_id', 'AAA')
% rslt = get_info_from_database('member', 'HSE')
% rslt = get_info_from_database('member', 'all')
% rslt = get_info_from_database('security', 'sec_id', 1, 'from', '01/01/2009', 'to', '31/12/2011')
% rslt = get_info_from_database('security', 'ticker', '^VNINDEX', 'from', '01/01/2009', 'to', '31/12/2011')
% rslt = get_info_from_database('last_price', 'sec_id', 1)
% rslt = get_info_from_database('market_impact_model', 4)
% rslt = get_info_from_database('sector', 4)
% rslt = get_info_from_database('sector-info', 'all')
% rslt = get_info_from_database('sector-info', 6)
% rslt = get_info_from_database('security-sector', 6)
% rslt = get_info_from_database('security-sector', 'banks')
% rslt = get_info_from_database('sector-2', 4) 					%UPDATED
% rslt = get_info_from_database('sector-info-2', 'all')			%UPDATED
% rslt = get_info_from_database('sector-info-2', 6)				%UPDATED
% rslt = get_info_from_database('sector-security-2', 6)			%UPDATED: get sector_id of specific security 
% rslt = get_info_from_database('security-sector-2', 6) %get security_id of sector 6
% rslt = get_info_from_database('security-sector-2', 'banks')	%UPDATED
% rslt = get_info_from_database('capital', 4)
% rslt = get_info_from_database('floating', 4)
% rslt = get_info_from_database('intraday', 'sec_id', 1, 'from', '01/01/2011', 'to', '31/12/2011')
% rslt = get_info_from_database('daily_stats', 'sec_id', 1)
% rslt = get_info_from_database('liquidity_measures', 'sec_id', 1, 'from', '01/01/2011', 'to', '31/12/2011')
% rslt = get_info_from_database('financial-report-item-id', 'current assets')
% rslt = get_info_from_database('financial_ratio', 'sec_id',sec_id, year, quarter)
% rslt = get_info_from_database('financial_ratio', 'sec_id',sec_id,'from', year1,'to',year2)

persistent dbConn

if isempty(dbConn)   
    dbConn = createdbConn;
end

switch lower(mode)
    
    % sec_id
    case 'sec_id'
        req_sql = sprintf(['SELECT security_id FROM security ' ...
            'WHERE short_name LIKE ''%s'''], varargin{1});
        curs = exec(dbConn, req_sql);
        rslt = get(fetch(curs), 'Data');
        rslt = rslt{1};
        
    % name and short_name    
    case 'name'
        req_sql = sprintf(['SELECT viet_name, eng_name, short_name FROM security ' ...
            'WHERE security_id = %d'], varargin{1});
        curs = exec(dbConn, req_sql);
        rslt = get(fetch(curs), 'Data');
        
    % ticker
    case 'ticker'
        req_sql = sprintf(['SELECT short_name FROM security ' ...
            'WHERE security_id = %d'], varargin{1});
        curs = exec(dbConn, req_sql);
        rslt = get(fetch(curs), 'Data');
        rslt = rslt{1};
    
    % ticksize    
    case 'ticksize'
        req_sql = sprintf(['SELECT threshold, tick_size '...
            'FROM price_scale '...
            'WHERE price_scale_id = (SELECT price_scale_id '...
            'FROM security WHERE security_id = %d)'], varargin{1});
        curs = exec(dbConn, req_sql);
        rslt = get(fetch(curs), 'Data');
        rslt = cell2mat(rslt);
                
    % trading_destination id
    case 'trading_destination_id'
        if isscalar(varargin{1})
            req_sql = sprintf(['SELECT trading_destination_id FROM security ' ...
                'WHERE security_id = %d'], varargin{1});
        else
            req_sql = sprintf(['SELECT trading_destination_id FROM security ' ...
                'WHERE short_name LIKE ''%s'''], varargin{1});
        end
        curs = exec(dbConn, req_sql);
        rslt = get(fetch(curs), 'Data');
        rslt = rslt{1};
        
    % trading_destination
    case 'trading_destination'
        if isscalar(varargin{1})
            req_sql = sprintf(['SELECT short_name '....
                'FROM trading_destination '...
                'WHERE trading_destination_id IN '...
                '(SELECT trading_destination_id FROM security ' ...
                'WHERE security_id = %d)'], varargin{1});
        else
            req_sql = sprintf(['SELECT short_name '....
                'FROM trading_destination '...
                'WHERE trading_destination_id IN '...
                '(SELECT trading_destination_id FROM security ' ...
                'WHERE short_name LIKE ''%s'')'], varargin{1});
        end
        curs = exec(dbConn, req_sql);
        rslt = get(fetch(curs), 'Data');
        rslt = rslt{1};
        
    case 'member'
        switch varargin{1}
            case 'all'
                req_sql = ['SELECT security_id '...
                    'FROM security '...
                    'WHERE security_type = 1'];
                
            otherwise
                req_sql = sprintf(['SELECT security_id '...
                    'FROM security '...
                    'WHERE security_type = 1 AND trading_destination_id IN '...
                    '(SELECT trading_destination_id '...
                    'FROM trading_destination '...
                    'WHERE short_name LIKE ''%s'')'], varargin{1});
                
                
        end
        curs = exec(dbConn, req_sql);
        rslt = get(fetch(curs), 'Data');
        rslt = cell2mat(rslt);
    % data    
    case 'security'        
        
        date_from_val = datenum(varargin{4}, 'dd/mm/yyyy');
        date_to_val   = datenum(varargin{6}, 'dd/mm/yyyy');
        
        year_from = year(date_from_val);
        year_to   = year(date_to_val);
        
        end_of_year = arrayfun(@(y)eomdate(y,12), year_from:year_to);
        nb_years = numel(end_of_year);
        if nb_years == 1
            date_val = [date_from_val;date_to_val];
        else
            date_val = NaN(2*nb_years,1);
            date_val(1:2:end) = [date_from_val end_of_year(1:end-1)+1];
            date_val(2:2:end) = [end_of_year(1:end-1)+1 date_to_val];
        end
        if isnumeric(varargin{2})
            % security id
            sec_id = varargin{2};
        else
            % ticker to security id
            rslt = get_info_from_database('sec_id', varargin{2});
            if strcmp(rslt, 'No Data')                
                return
            else
                sec_id = rslt;
            end
        end
        rslt = [];        
        for y=1:nb_years
            table_name = sprintf('trading_daily_%d',year_from+y-1);            
            date_from = datestr(date_val(2*y-1), 'yyyy-mm-dd');
            date_to   = datestr(date_val(2*y), 'yyyy-mm-dd');        
            
            req_sql = sprintf(['SELECT date, volume, open_prc, high_prc, low_prc, close_prc ', ...
                sprintf('FROM %s ', table_name), ...
                'WHERE security_id = %d ', ...
                'AND date BETWEEN ''%s'' and ''%s'''], sec_id, date_from, date_to);
            curs = exec(dbConn, req_sql);
            temp_rslt = get(fetch(curs), 'Data');
            if ~strcmpi(temp_rslt{1}, 'no data')
                rslt = vertcat(rslt,horzcat(datenum(temp_rslt(:,1), 'yyyy-mm-dd'), cell2mat(temp_rslt(:,2:end))));                
            end            
        end
        
    case 'last_price'
        yr = year(now);
        table_name = sprintf('trading_daily_%d', yr);
        req_sql = sprintf(['SELECT date, close_prc '...
            'FROM %s JOIN (', ...
            'SELECT security_id sec_id, max(date) as maxdate '...
            'FROM %s ' ...
            'GROUP BY security_id) as x ' ...
            'ON %s.security_id = sec_id AND date = maxdate ' ...
            'WHERE security_id = %d'], table_name, table_name, table_name, varargin{2});
        curs = exec(dbConn, req_sql);
        rslt = get(fetch(curs), 'Data');
    
    % sector
    case 'sector'
        security_id = varargin{1};
        if ischar(security_id)
            security_id = get_info_from_database('sec_id', security_id);
            if ischar(security_id)
                fprintf('get_info_from_database:mode <%s>: cannot find security_id of %s',...
                    mode, varargin{1});
                return
            end
        end
        req_sql = sprintf(['SELECT eng_name '...
            'FROM sector '...
            'WHERE sector_id IN '...
            '(SELECT sector_id FROM security WHERE security_id = %d)'], security_id);
        curs = exec(dbConn, req_sql);
        rslt = get(fetch(curs), 'Data');
        rslt = rslt{1};
        
    % security_id from sector
    case 'security-sector'
        sector_id = varargin{1};
        if ischar(sector_id)
            sector_id = get_info_from_database('sector-info', sector_id);
        end
        req_sql = sprintf(['SELECT security_id '...
            'FROM security '...
            'WHERE sector_id = %d'], sector_id);
        curs = exec(dbConn, req_sql);
        rslt = get(fetch(curs), 'Data');
        rslt = cell2mat(rslt);
    		   
    case 'sector-info'
        sub_mode = varargin{1};
        if ischar(sub_mode)
            switch lower(sub_mode)
                case 'all'
                    req_sql = 'SELECT sector_id, eng_name FROM sector';
                otherwise
                    req_sql = sprintf(['SELECT sector_id '...
                        'FROM sector '...
                        'WHERE eng_name LIKE ''%s'''], sub_mode);
            end
        else
            req_sql = sprintf('SELECT sector_id, eng_name FROM sector WHERE sector_id = %d', sub_mode);
        end        
        curs = exec(dbConn, req_sql);
        rslt = get(fetch(curs), 'Data');
        if size(rslt, 2) == 1
            rslt = rslt{1};
        end
    
	%update new sector 
	case 'sector-2'
        security_id = varargin{1};
        if ischar(security_id)
            security_id = get_info_from_database('sec_id', security_id);
            if ischar(security_id)
                fprintf('get_info_from_database:mode <%s>: cannot find security_id of %s',...
                    mode, varargin{1});
                return
            end
        end
        req_sql = sprintf(['SELECT eng_name '...
            'FROM sector_2 '...
            'WHERE sector_id IN '...
            '(SELECT sector_id FROM security WHERE security_id = %d)'], security_id);
        curs = exec(dbConn, req_sql);
        rslt = get(fetch(curs), 'Data');
        rslt = rslt{1};
    
    case 'sector-security-2'
        
        sector_id = varargin{1};
        if ischar(sector_id)
            sector_id = get_info_from_database('sector-info-2', sector_id);
        end
        req_sql = sprintf(['select s.sector_id ' ...
            'FROM sector_security ss, sector_2 s ' ...
            'where s.sector_id=ss.sector_id and security_id=%d'], sector_id);
        curs = exec(dbConn, req_sql);
        rslt = get(fetch(curs), 'Data');
        rslt = cell2mat(rslt);  
        
    case 'security-sector-2'        
        sector_id = varargin{1};
        if ischar(sector_id)
            sector_id = get_info_from_database('sector-info-2', sector_id);
        end
        req_sql = sprintf(['select DISTINCT security_id ' ...
            'FROM sector_security ' ...
            'WHERE sector_id=%d'], sector_id);
        curs = exec(dbConn, req_sql);
        rslt = get(fetch(curs), 'Data');
        rslt = cell2mat(rslt);

	case 'sector-info-2'
        sub_mode = varargin{1};
        if ischar(sub_mode)
            switch lower(sub_mode)
                case 'all'
                    req_sql = 'SELECT sector_id, eng_name FROM sector_2';
                otherwise
                    req_sql = sprintf(['SELECT sector_id '...
                        'FROM sector_2 '...
                        'WHERE eng_name LIKE ''%s'''], sub_mode);
            end
        else
            req_sql = sprintf('SELECT sector_id, eng_name FROM sector_2 WHERE sector_id = %d', sub_mode);
        end        
        curs = exec(dbConn, req_sql);
        rslt = get(fetch(curs), 'Data');
        if size(rslt, 2) == 1
            rslt = rslt{1};
        end    
    
    % financial ratio
    % rslt = get_info_from_database('financial_ratio', sec_id, yr, quarter)
    case 'financial_ratio'
        security_id = varargin{2};
        if(isscalar(varargin{3}))
            yr = varargin{3};
            quarter = varargin{4};
                        
            req_sql = sprintf('SELECT * FROM `financial_ratio` WHERE `security_id` = %d AND quarter = %d AND `year`=%d',security_id,quarter,yr);
        else
            year1 = varargin{4};
            year2 = varargin{6};
            req_sql = sprintf(['SELECT * FROM `financial_ratio` WHERE `security_id` = %d AND `year` >= %d AND `year`<=%d ' ...
                'order by `year` ASC, `quarter` ASC'],security_id,year1,year2);
        end
        curs = exec(dbConn, req_sql);
        rslt = get(fetch(curs), 'Data');    
    
    case 'floating'
        security_id = varargin{1};
        if ischar(security_id)
            security_id = get_info_from_database('sec_id', security_id);
            if ischar(security_id)
                fprintf('get_info_from_database:mode <%s>: cannot find security_id of %s',...
                    mode, varargin{1});
                return
            end
        end
        req_sql = sprintf('SELECT floating_shares FROM security WHERE security_id = %d', security_id);
        curs = exec(dbConn, req_sql);
        rslt = get(fetch(curs), 'Data');
        rslt = rslt{1};
        
    case 'last_n_lines'
        req_sql = sprintf(['SELECT date, volume, open_prc, high_prc, low_prc, close_prc FROM '...
            sprintf('(SELECT * FROM trading_daily_%d ', year(now)) ...
            'WHERE security_id = %d ORDER BY date DESC) as x '...
            'LIMIT %d'], varargin{2}, varargin{4});
        curs = exec(dbConn, req_sql);
        rslt = get(fetch(curs), 'Data');
        if ~strcmpi(rslt{1}, 'no data')
            rslt = horzcat(datenum(rslt(:,1), 'yyyy-mm-dd'), cell2mat(rslt(:,2:end)));
        end
        
    case 'security_idx'
        table_name = sprintf('trading_daily_%d', year(now));
        req_sql = sprintf(['SELECT j.date, j.stock, j.ind FROM '...
            '(SELECT td1.date date, td1.close_prc stock, td2.close_prc ind '...
            'FROM %s td1, %s td2 '...
            'WHERE td1.security_id = %d and td2.security_id = %d '...
            'AND td1.date = td2.date ORDER BY td1.date DESC) as j LIMIT %d'],...
            table_name, table_name, varargin{2}, varargin{4}, varargin{6});
        curs = exec(dbConn, req_sql);
        rslt = get(fetch(curs), 'Data');
        if ~strcmpi(rslt{1}, 'no data')
            rslt = horzcat(datenum(rslt(:,1), 'yyyy-mm-dd'), cell2mat(rslt(:,2:end)));
        end
        
        
    case 'market_impact_model'
        req_sql = sprintf(['SELECT const, kappa, gamma, beta FROM market_impact_model '...
            'WHERE security_id = %d'], varargin{1});
        curs = exec(dbConn, req_sql);
        rslt = get(fetch(curs), 'Data');
        if ~strcmpi(rslt{1}, 'no data')
            rslt = cell2mat(rslt);
        else
            rslt = [];
        end
        
    case 'intraday'
        date_from_val = datenum(varargin{4}, 'dd/mm/yyyy');
        date_to_val   = datenum(varargin{6}, 'dd/mm/yyyy');
        if date_from_val == date_to_val
            date_to_val = date_to_val+1;
        end
        year_from = year(date_from_val);
        year_to   = year(date_to_val);
        
        end_of_year = arrayfun(@(y)eomdate(y,12), year_from:year_to);
        nb_years = numel(end_of_year);
        if nb_years == 1
            date_val = [date_from_val;date_to_val];
        else
            date_val = NaN(2*nb_years,1);
            date_val(1:2:end) = [date_from_val end_of_year(1:end-1)+1];
            date_val(2:2:end) = [end_of_year(1:end-1)+1 date_to_val];
        end
        rslt = [];        
        for y=1:nb_years
            table_name = sprintf('intraday_%d',year_from+y-1);            
            date_from = datestr(date_val(2*y-1), 'yyyy-mm-dd');
            date_to   = datestr(date_val(2*y), 'yyyy-mm-dd');        
            
            req_sql = sprintf(['SELECT date, volume, price, bid, bid_size, ask, ask_size, sell ', ...
                sprintf('FROM %s ', table_name), ...
                'WHERE security_id = %d ', ...
                'AND date BETWEEN ''%s'' and ''%s'''], varargin{2}, date_from, date_to);
            curs = exec(dbConn, req_sql);
            temp_rslt = get(fetch(curs), 'Data');
            if ~(isnumeric(temp_rslt) || strcmpi(temp_rslt{1}, 'no data'))
                dates = cellfun(@(c)datenum(c(1:19), 'yyyy-mm-dd HH:MM:SS'), temp_rslt(:,1));
                rslt = vertcat(rslt,horzcat(dates, cell2mat(temp_rslt(:,2:end))));
            end            
        end
        if ~isempty(rslt)
            rslt = struct('date', rslt(:,1),...
                'volume', rslt(:,2),...
                'price', rslt(:,3),...
                'bid', rslt(:,4),...
                'bid_size', rslt(:,5),...
                'ask', rslt(:,6),...
                'ask_size', rslt(:,7),...
                'sell', rslt(:,8));
        end
        
    case 'daily_stats'
        rslt = [];
        req_sql = sprintf(['SELECT mean_ret, std_ret, median_ret, skew_ret, kurtosis_ret, GK_vol, max_dd, ', ...
            'min_turnover_pct, med_turnover_pct, max_turnover_pct,', ...
            'min_turnover_cash, med_turnover_cash, max_turnover_cash,', ...
            'alpha, beta, tracking_error ',...
            'FROM daily_stats ', ...
            'WHERE security_id = %d '], varargin{2});
        curs = exec(dbConn, req_sql);
        temp_rslt = get(fetch(curs), 'Data');
        if ~strcmpi(temp_rslt{1}, 'no data')
            temp_rslt = cell2mat(temp_rslt);
            colnames = {'mean_ret', 'std_ret', 'median_ret',...
                'skew_ret', 'kurtosis_ret', 'GK_vol', 'max_dd', ...
                'min_turnover_pct', 'med_turnover_pct', 'max_turnover_pct', ...
                'min_turnover_cash', 'med_turnover_cash','max_turnover_cash', ...
                'alpha', 'beta', 'tracking_error'};            
            if(varargin{2} == 1)
                rslt = struct();
                for c=1:numel(colnames)
                    rslt = setfield(rslt, colnames{c}, temp_rslt(:,c));
                end
            else
                rslt = temp_rslt;
            end
        end
        
    case 'liquidity_measures'
        date_from_val = datenum(varargin{4}, 'dd/mm/yyyy');
        date_to_val   = datenum(varargin{6}, 'dd/mm/yyyy');
        
        year_from = year(date_from_val);
        year_to   = year(date_to_val);
        
        end_of_year = arrayfun(@(y)eomdate(y,12), year_from:year_to);
        nb_years = numel(end_of_year);
        if nb_years == 1
            date_to_val = date_to_val + 1;
            date_val    = [date_from_val; date_to_val];
        else
            date_val = NaN(2*nb_years,1);
            date_val(1:2:end) = [date_from_val end_of_year(1:end-1)+1];
            date_val(2:2:end) = [end_of_year(1:end-1)+1 date_to_val];
        end
        tmp_rslt = [];        
        for y=1:nb_years
            table_name = sprintf('liquidity_measures_%d',year_from+y-1);            
            date_from = datestr(date_val(2*y-1), 'yyyy-mm-dd');
            date_to   = datestr(date_val(2*y), 'yyyy-mm-dd');        
            
            req_sql = sprintf(['SELECT * ', ...
                sprintf('FROM %s ', table_name), ...
                'WHERE security_id = %d ', ...
                'AND date BETWEEN ''%s'' and ''%s'''], varargin{2}, date_from, date_to);
            curs = exec(dbConn, req_sql);
            temp_rslt = get(fetch(curs), 'Data');
            if ~(isnumeric(temp_rslt) || strcmpi(temp_rslt{1}, 'no data'))                
                dates = cellfun(@(c)datenum(c(1:19), 'yyyy-mm-dd HH:MM:SS'), temp_rslt(:,3));
                tmp_rslt = vertcat(tmp_rslt,horzcat(dates, cell2mat(temp_rslt(:,4:end))));
            end
        end
        if ~isempty(tmp_rslt)
            curs = exec(dbConn, sprintf('SHOW FIELDS FROM %s', table_name));
            colnames = get(fetch(curs), 'Data');
            rslt = struct();
            for i=1:size(tmp_rslt, 2)
                rslt = setfield(rslt, colnames{i+2}, tmp_rslt(:,i));
            end            
        else 
            rslt = [];
        end
        
    case 'financial-report-item-id'
        if nargin == 2
            viet_name = varargin{1};
            eng_name = varargin{1};
        elseif nargin == 3
            viet_name = varargin{1};
            eng_name = varargin{2};
        else
            fprintf('get_info_from_database:mode <%s> requires at least 1 parameter.\n', mode);
        end
        sql_template = 'SELECT item_id FROM financial_report_items WHERE viet_name LIKE ''%%%s%%'' OR eng_name LIKE ''%%%s%%''';
        curs = exec(dbConn, sprintf(sql_template, viet_name, eng_name));
        temp_rslt = get(fetch(curs), 'Data');
        if strcmpi(temp_rslt{1}, 'no data')            
            rslt = []; 
        else
            rslt = temp_rslt{1};
        end
        
    case 'capital'
        security_id = varargin{1};
        if ischar(security_id)
            security_id = get_info_from_database('sec_id', security_id);
            if ischar(security_id)
                fprintf('get_info_from_database:mode <%s>: cannot find security_id of %s',...
                    mode, varargin{1});
                return
            end
        end
        try
            last_price = get_info_from_database('last_price', 'sec_id', security_id);
            floating = get_info_from_database('floating', security_id);
            rslt = last_price{2} * floating;
        catch
            rslt = [];
            fprintf('get_info_from_database:mode <%s> cannot get capital for this security_id.\n', mode);
        end
        
        
    otherwise
        fprintf('get_info_from_database:mode <%s> is not recognized.\n', mode);
end
end